﻿using Microsoft.VisualStudio.TestTools.UnitTesting;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Sustainalytics.ESGRatings.Service.Tests.KeyESGIssues
{
    [TestClass]
    public class KeyESGIssuesFileProcessing
    {
        [TestMethod]
        public void CheckTemplateIntegrity()
        {
            SpreadsheetGear.IWorkbook book = SpreadsheetGear.Factory.GetWorkbook(@"..\..\KeyESGIssues\Template\Key ESG Issues Management template.xlsx");
            
            var indicatorsSheet = book.Worksheets[0];
            
            var valuesSheet = book.Worksheets[1];

            var eventIndicators = book.Worksheets[2];
            
            Assert.AreEqual("Indicators",indicatorsSheet.Name,"Indicators sheet name is inconsistent!");
            
            Assert.AreEqual("Values", valuesSheet.Name, "Values sheet name is inconsistent!");
            
            Assert.AreEqual("Event indicators", eventIndicators.Name, "Event indicators sheet name is inconsistent!");
        }

        [TestMethod]
        public void ValidateRPIndicatorCode()
        {
            SpreadsheetGear.IWorkbook book = SpreadsheetGear.Factory.GetWorkbook(@"..\..\KeyESGIssues\Template\Key ESG Issues Management template.xlsx");

            var indicatorsSheet = book.Worksheets[0];

            List<string> rpIndicatorsFromTemplate = new List<string>();

            for (int i = 5; ; )
            {
                if (indicatorsSheet.Cells[0, i].Value == null)
                    break;

                rpIndicatorsFromTemplate.Add(indicatorsSheet.Cells[0, i].Value.ToString());
                i++;
            }

            Assert.AreEqual(155, rpIndicatorsFromTemplate.Count,"The expected number of Indicators is different!");

            Assert.AreEqual("E.1.1", rpIndicatorsFromTemplate[0], "E.1.1 Indicator expected found:" + rpIndicatorsFromTemplate[0]);

        }

        [TestMethod]
        public void ValidateSubIndustryCodeAndKeyIssueCode()
        {
            SpreadsheetGear.IWorkbook book = SpreadsheetGear.Factory.GetWorkbook(@"..\..\KeyESGIssues\Template\Key ESG Issues Management template.xlsx");

            var indicatorsSheet = book.Worksheets[0];

            int rowNumber = 2;
            int subIndustryCodeCell = 1;
            int keyEsgCodeCell = 3;

            List<KeyESGIssues> testESGIssues = new List<KeyESGIssues>();
            for (int i = 5; ; )
            {
                if ((indicatorsSheet.Cells[rowNumber, subIndustryCodeCell].Value == null) || (indicatorsSheet.Cells[rowNumber, keyEsgCodeCell].Value == null))
                    break; // throw error

                testESGIssues.Add(new KeyESGIssues()
                {
                    Id = Guid.NewGuid(),
                    SubIndustryCode = int.Parse(indicatorsSheet.Cells[rowNumber, subIndustryCodeCell].Value.ToString()),
                    KeyESGIssueCode = int.Parse(indicatorsSheet.Cells[rowNumber, keyEsgCodeCell].Value.ToString())

                });

                rowNumber++;
            }

            Assert.AreEqual(1154, testESGIssues.Count,"Expected 1154 entries, got " + testESGIssues.Count.ToString());

        }

        [TestMethod]
        public void ValidateRPIndicators() // Process Indicators Sheet
        {
            SpreadsheetGear.IWorkbook book = SpreadsheetGear.Factory.GetWorkbook(@"..\..\KeyESGIssues\Template\Key ESG Issues Management template.xlsx");

            var indicatorsSheet = book.Worksheets[0];

            int rowNumber = 2;
            int subIndustryCodeCell = 1;
            int keyEsgCodeCell = 3;

            List<KeyESGIssues> testESGIssues = new List<KeyESGIssues>();
            int numberOfRPIndicators = GetNumberOfRPIndicators();
            for (int i = 5; ; )
            {
                if ((indicatorsSheet.Cells[rowNumber, subIndustryCodeCell].Value == null) || (indicatorsSheet.Cells[rowNumber, keyEsgCodeCell].Value == null))
                    break; // throw error

                KeyESGIssues tempEsgIssue = new KeyESGIssues();

                List<string> indicators = new List<string>();

                for (int j = 0; j <= numberOfRPIndicators; j++)
                {
                    if (indicatorsSheet.Cells[rowNumber, j + 5].Value != null)
                        if ((indicatorsSheet.Cells[rowNumber, j + 5].Value.ToString() == "1") || (indicatorsSheet.Cells[rowNumber, j + 5].Value.ToString() == "-1"))
                            indicators.Add(indicatorsSheet.Cells[0, j + 5].Value.ToString());
                }

                tempEsgIssue.Id = Guid.NewGuid();
                tempEsgIssue.SubIndustryCode = int.Parse(indicatorsSheet.Cells[rowNumber, subIndustryCodeCell].Value.ToString());
                tempEsgIssue.KeyESGIssueCode = int.Parse(indicatorsSheet.Cells[rowNumber, keyEsgCodeCell].Value.ToString());
                tempEsgIssue.RPIndicators = indicators;

                testESGIssues.Add(tempEsgIssue);

                rowNumber++;
            }

            Assert.AreEqual(1154, testESGIssues.Count, "Expected 1154 entries, got " + testESGIssues.Count.ToString());

            Assert.AreEqual("E.1.10", testESGIssues[1].RPIndicators[0], "Expected indicator E.1.10 and got " + testESGIssues[1].RPIndicators[0]);
            Assert.AreEqual("E.1.11", testESGIssues[1].RPIndicators[1], "Expected indicator E.1.11 and got " + testESGIssues[1].RPIndicators[1]);
            Assert.AreEqual("E.1.5", testESGIssues[1].RPIndicators[2], "Expected indicator E.1.5 and got " + testESGIssues[1].RPIndicators[2]);
            Assert.AreEqual("E.1.6", testESGIssues[1].RPIndicators[3], "Expected indicator E.1.6 and got " + testESGIssues[1].RPIndicators[3]);
            Assert.AreEqual("E.1.7", testESGIssues[1].RPIndicators[4], "Expected indicator E.1.7 and got " + testESGIssues[1].RPIndicators[4]);
            Assert.AreEqual("E.1.8", testESGIssues[1].RPIndicators[5], "Expected indicator E.1.8 and got " + testESGIssues[1].RPIndicators[5]);
            Assert.AreEqual("E.1.9", testESGIssues[1].RPIndicators[6], "Expected indicator E.1.9 and got " + testESGIssues[1].RPIndicators[6]);

        }

        [TestMethod]
        public void ValidateImpacts()
        {
            SpreadsheetGear.IWorkbook book = SpreadsheetGear.Factory.GetWorkbook(@"..\..\KeyESGIssues\Template\Key ESG Issues Management template.xlsx");

            var valuesSheet = book.Worksheets[1];

            int rowNumber = 2;
            int subIndustryCodeCell = 1;
            int keyEsgCodeCell = 3;

            List<KeyESGIssues> testESGIssues = new List<KeyESGIssues>();

            for (int i = 5; ; )
            {
                if ((valuesSheet.Cells[rowNumber, subIndustryCodeCell].Value == null) || (valuesSheet.Cells[rowNumber, keyEsgCodeCell].Value == null))
                    break; // throw error

                KeyESGIssues tempEsgIssue = new KeyESGIssues();
                
                tempEsgIssue.Id = Guid.NewGuid();

                if (valuesSheet.Cells[rowNumber, i].Value != null)
                    if ((int.Parse(valuesSheet.Cells[rowNumber, i].Value.ToString()) >= 0) && (int.Parse(valuesSheet.Cells[rowNumber, i].Value.ToString()) <= 10))
                        tempEsgIssue.SustainabilityImpact = int.Parse(valuesSheet.Cells[rowNumber, i].Value.ToString());

                if (valuesSheet.Cells[rowNumber, i + 1].Value != null)
                    if ((int.Parse(valuesSheet.Cells[rowNumber, i + 1].Value.ToString()) >= 0) && (int.Parse(valuesSheet.Cells[rowNumber, i + 1].Value.ToString()) <= 10))
                    tempEsgIssue.BussinessImpact = int.Parse(valuesSheet.Cells[rowNumber, i + 1].Value.ToString());

                testESGIssues.Add(tempEsgIssue);

                rowNumber++;
            }

            Assert.AreEqual(1, int.Parse(valuesSheet.Cells[2, 5].Value.ToString()), "Expected Sustainalytics Impact Value 1, Received: " + int.Parse(valuesSheet.Cells[2, 5].Value.ToString()));
            Assert.AreEqual(2, int.Parse(valuesSheet.Cells[2, 6].Value.ToString()), "Expected Business Impact Value 2, Received: " + int.Parse(valuesSheet.Cells[2, 6].Value.ToString()));
        }

        [TestMethod]
        public void ValidateEventIndicators()
        {
            SpreadsheetGear.IWorkbook book = SpreadsheetGear.Factory.GetWorkbook(@"..\..\KeyESGIssues\Template\Key ESG Issues Management template.xlsx");

            var eventIndicatorSheet = book.Worksheets[2];

            int rowNumber = 2;
            int subIndustryCodeCell = 1;
            int keyEsgCodeCell = 3;

            List<KeyESGIssues> testESGIssues = new List<KeyESGIssues>();

            int numberOfEventIndicators = GetNumberOfEventIndicators();
            for (int i = 5; ; )
            {
                if ((eventIndicatorSheet.Cells[rowNumber, subIndustryCodeCell].Value == null) || (eventIndicatorSheet.Cells[rowNumber, keyEsgCodeCell].Value == null))
                    break; // throw error

                KeyESGIssues tempEsgIssue = new KeyESGIssues();

                List<string> indicators = new List<string>();

                for (int j = 0; j <= numberOfEventIndicators; j++)
                {
                    if (eventIndicatorSheet.Cells[rowNumber, j + 5].Value != null)
                        if (eventIndicatorSheet.Cells[rowNumber, j + 5].Value.ToString() == "1")
                            indicators.Add(eventIndicatorSheet.Cells[1, j + 5].Value.ToString());
                }

                tempEsgIssue.Id = Guid.NewGuid();

                tempEsgIssue.SubIndustryCode = int.Parse(eventIndicatorSheet.Cells[rowNumber, subIndustryCodeCell].Value.ToString());
                tempEsgIssue.KeyESGIssueCode = int.Parse(eventIndicatorSheet.Cells[rowNumber, keyEsgCodeCell].Value.ToString());
                tempEsgIssue.EventIndicators = indicators;

                testESGIssues.Add(tempEsgIssue);

                rowNumber++;
            }

            Assert.AreEqual(1154, testESGIssues.Count, "Expected 1154 entries, got " + testESGIssues.Count.ToString());

            Assert.AreEqual("Conservation and Land Use", testESGIssues[1].EventIndicators[0], "Expected Event indicator Conservation and Land Use and got " + testESGIssues[1].EventIndicators[0]);
            Assert.AreEqual("Energy Use and GHG Emissions", testESGIssues[1].EventIndicators[1], "Expected Event indicator E.1.11 and got " + testESGIssues[1].EventIndicators[1]);
            Assert.AreEqual("Health and Safety", testESGIssues[1].EventIndicators[2], "Expected Event indicator E.1.5 and got " + testESGIssues[1].EventIndicators[2]);
            Assert.AreEqual("Labour Relations", testESGIssues[1].EventIndicators[3], "Expected Event indicator E.1.6 and got " + testESGIssues[1].EventIndicators[3]);
            Assert.AreEqual("Basic Labour Standards", testESGIssues[1].EventIndicators[4], "Expected Event indicator E.1.7 and got " + testESGIssues[1].EventIndicators[4]);
            Assert.AreEqual("Marketing Practices", testESGIssues[1].EventIndicators[5], "Expected Event indicator E.1.8 and got " + testESGIssues[1].EventIndicators[5]);
            Assert.AreEqual("Complicity in Human Rights Violations", testESGIssues[1].EventIndicators[6], "Expected Event indicator E.1.9 and got " + testESGIssues[1].EventIndicators[6]);
        }

        public int GetNumberOfRPIndicators()
        {
            SpreadsheetGear.IWorkbook book = SpreadsheetGear.Factory.GetWorkbook(@"..\..\KeyESGIssues\Template\Key ESG Issues Management template.xlsx");

            var indicatorsSheet = book.Worksheets[0];

            int numberOfRPIndicators = 0;

            for (int i = 5; ; )
            {
                if (indicatorsSheet.Cells[0, i].Value == null)
                    break;

                i++;
                numberOfRPIndicators++;
            }

            return numberOfRPIndicators;
        }

        public int GetNumberOfEventIndicators()
        {
            SpreadsheetGear.IWorkbook book = SpreadsheetGear.Factory.GetWorkbook(@"..\..\KeyESGIssues\Template\Key ESG Issues Management template.xlsx");

            var indicatorsSheet = book.Worksheets[2];

            int numberOfEventIndicators = 0;

            for (int i = 5; ; )
            {
                if (indicatorsSheet.Cells[1, i].Value == null)
                    break;

                i++;
                numberOfEventIndicators++;
            }

            return numberOfEventIndicators;
        }

        [TestMethod]
        public void ProcessXLSX()
        {
            SpreadsheetGear.IWorkbook book = SpreadsheetGear.Factory.GetWorkbook(@"..\..\KeyESGIssues\Template\Key ESG Issues Management template.xlsx");

            Sustainalytics.ESGRatings.BusinessLogic.KeyESGIssuesManagement issues = new BusinessLogic.KeyESGIssuesManagement();

            //issues.EnsureTemplateConsistency(book);
        }
    }

    public class KeyESGIssues {
        public int BussinessImpact { get; set; }
        public List<string> EventIndicators { get; set; } // will be GUID in the real model, used string to get the indicator number more easly
        public Guid Id { get; set; }
        public int KeyESGIssueCode { get; set; }
        public string Name { get; set; }
        public List<string> RPIndicators { get; set; } // will be GUID in the real model, used string to get the indicator number more easly
        public int SubIndustryCode { get; set; }
        public int SustainabilityImpact { get; set; }
    }

    public class KeyESGIssuesCompanies {
        public Guid CompanyId { get; set; }
        public string CompanyName {get; set;}
        public string CompanyNameLower { get; set; }
        public Guid Id { get; set; }
        public List<KeyESGIssue> KeyESGIssues { get; set; }
        public int SubIndustryCode { get; set; }
    }

    public class KeyESGIssue
    {
        public Guid Id { get; set; }
        public string Name { get; set; }
        public string Code { get; set; }
    }
}
